package com.onlinesys.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.onlinesys.beans.ProductRepository;
import com.onlinesys.database.DBManager;
import com.onlinesys.protocol.ProtocolConstant;
import com.onlinesys.service.ProductReprocessService;

public class ProductReprocessServiceImpl implements ProductReprocessService {

    @Override
    public int getProductReprocessTotalCount() throws SQLException {
        String sql = "SELECT count(1)  FROM prod_repository WHERE type = " + ProtocolConstant.PRODUCT_TYPE_REPAIR_ONLINE;
        int total = 0;
        Connection conn = DBManager.getDBConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            total = rs.getInt(1);
        }
        DBManager.free(rs, stmt, conn);
        return total;
    }

    @Override
    public List<ProductRepository> getProductReprocesses(int startRow, int pageSize) throws SQLException {
        String sql = "SELECT pp.id id, pp.quantity quantity, pp.drawingNum drawingNum, pp.updateDate updateDate, pd.vendor vendor, pd.area area" +
        " FROM prod_drawingnum pd, prod_repository pp WHERE pp.drawingNum = pd.drawingNum AND pp.type = " + ProtocolConstant.PRODUCT_TYPE_REPAIR_ONLINE
                + " ORDER BY pp.updateDate desc LIMIT " + startRow + "," + pageSize;
        List<ProductRepository> pps = new ArrayList<ProductRepository>();
        Connection conn = DBManager.getDBConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
        	ProductRepository pp = new ProductRepository();
            pp.setId(rs.getString("id"));
            pp.setQuantity(rs.getInt("quantity"));
            pp.setDrawingNum(rs.getString("drawingNum"));
            pp.setUpdateDate(rs.getTimestamp("updateDate"));
            pp.setVendor(rs.getString("vendor"));
            pp.setArea(rs.getDouble("area"));
            pps.add(pp);
        }
        DBManager.free(rs, stmt, conn);
        return pps;
    }

    @Override
    public List<ProductRepository> searchProductReprocesses(int startRow, int pageSize, ProductRepository pd) throws SQLException {
        StringBuilder whereStr = new StringBuilder();
        if (pd.getDrawingNum() != null && !"".equals(pd.getDrawingNum().trim())) {
            whereStr.append(" and pp.drawingNum like '%" + pd.getDrawingNum() + "%'");
        }
        if (pd.getVendor() != null && !"".equals(pd.getVendor().trim())) {
            whereStr.append(" and pd.vendor like '%" + pd.getVendor() + "%'");
        }
        if (pd.getArea() != null) {
            whereStr.append(" and pd.area = " + pd.getArea());
        }
        String sql = "SELECT pp.id id, pp.quantity quantity, pp.drawingNum drawingNum, pp.updateDate updateDate, pd.vendor vendor, pd.area area " +
        		" FROM prod_drawingnum pd, prod_repository pp WHERE pp.drawingNum = pd.drawingNum AND pp.type = " + ProtocolConstant.PRODUCT_TYPE_REPAIR_ONLINE + whereStr
        		+ " ORDER BY pp.updateDate desc LIMIT " + startRow + "," + pageSize;
        List<ProductRepository> pps = new ArrayList<ProductRepository>();
        Connection conn = DBManager.getDBConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
        	ProductRepository pp = new ProductRepository();
            pp.setId(rs.getString("id"));
            pp.setQuantity(rs.getInt("quantity"));
            pp.setDrawingNum(rs.getString("drawingNum"));
            pp.setUpdateDate(rs.getTimestamp("updateDate"));
            pp.setVendor(rs.getString("vendor"));
            pp.setArea(rs.getDouble("area"));
            pps.add(pp);
        }
        DBManager.free(rs, stmt, conn);
        return pps;
    }

    @Override
    public int searchProductReprocessTotalCount(ProductRepository pd) throws SQLException {
        StringBuilder whereStr = new StringBuilder();
        if (pd.getDrawingNum() != null && !"".equals(pd.getDrawingNum().trim())) {
            whereStr.append(" and pp.drawingNum like '%" + pd.getDrawingNum() + "%'");
        }
        if (pd.getModelSerialNum() != null && !"".equals(pd.getModelSerialNum().trim())) {
            whereStr.append(" and pp.modelSerialNum like '%" + pd.getModelSerialNum() + "%'");
        }
        if (pd.getQuantity() != 0) {
            whereStr.append(" and pp.quantity=" + pd.getQuantity());
        }
        if (pd.getVendor() != null && !"".equals(pd.getVendor().trim())) {
            whereStr.append(" and pd.vendor like '%" + pd.getVendor() + "%'");
        }
        if (pd.getArea() != null) {
            whereStr.append(" and pd.area = " + pd.getArea());
        }
        String sql = "SELECT count(1) FROM prod_drawingnum pd, prod_repository pp WHERE pp.drawingNum = pd.drawingNum AND pp.type = " + ProtocolConstant.PRODUCT_TYPE_REPAIR_ONLINE + whereStr;
        Connection conn = DBManager.getDBConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
        DBManager.free(rs, stmt, conn);
        return count;
    }

}
